Data Validation: What, How, Why?
Have you ever wanted a way to ensure the data you collect is accurate, qualitative, and healthy? If you have, you're not alone. Fortunately, data validation testing already provides a solution to the problem.
Studies show that many enterprises base their decisions on outdated, uncorroborated data, which has negatively impacted their sustainability. It is a problem that will continue to grow as the world becomes more reliant on analytics. With data validation testing, enterprises can verify the accuracy and validity of their databases and make better decisions. This article covers all you need to know on the subject.
FREE eBook
Successfully Implementing
Data Quality Improvements
This free eBook unveils one of the most important secrets of successful data quality improvement projects.
Useful definitions
Data integrity testing: data integrity deals with the quality of data in a database and the level to which users examine data quality, reliability and integrity. Data integrity testing ensures all the data in the database is accurate and that it functions as expected.
Data Migration testing: a process that verifies migrations from legacy systems to new systems. It ensures the process requires limited downtime, that the data maintains its integrity, and that it isn’t lost.
Training sets and testing sets: training sets are used to construct data models, and testing sets are used to validate the models created. Data points used utilized in the training set aren’t included in the testing/validation set.
What is data validation?
Data validation or data validation testing, as used in computer science, refers to the activities/operations undertaken to refine data, so it attains a high degree of quality. I.e., that it is both useful and accurate. It is an automated check performed to ensure that data input is rational and acceptable. The data validation process relies on routines (also known as "validation rules" or "validation constraints"). These rules may be executed through the automated capabilities of a data dictionary or through the integration of explicit program validation logic.
Importance of data validation
Validating the clarity, accuracy, and particulars of your data are vital to prevent project irregularities. If you do not validate your dataset, you'll likely base your decisions on an imperfect dataset that does not fully reflect the situation at hand.
Furthermore, through applications such as continuous data testing, data validation can also identify and eliminate well-formed but incorrect data.
Note: Remember that while the verification of your data inputs and values is essential, it is vital to validate your data models as well. If your model isn't built or structured the right way, you will encounter run into challenges when attempting to use your data files in various applications and software.
How data validation testing works
Data validation testing involves four significant steps
1 Planning
This is the most crucial part of the process, and it involves creating a proper strategy for the data validation process. It addresses the overall possibility that there will be inconsistencies in the source data, and the actions required to resolve the problems identified.
2 Data lineage validation
This stage involves making sure that all the relevant data is present from source to sink. This process determines the number of records, data size, and comparison of source and target based on the data field.
This stage may also involve the use of machine learning to validate the database by comparing testing datasets and training datasets. Comparing the test and training data limits the effects of data irregularities and makes it easier to comprehend the characteristics of the data model.
Training Vs. Testing data
A training set is used to build up a database model, while a test set validates the model that has been created. Simply put, the training data fits the model, and the testing data is used to test it.
3 Validation of data formatting
The target here is to ensure that the data is legible in the target system.
The end-users of the data should clearly understand the data so they can determine whether or not it meets expectations.
4 Sampling
Before testing large datasets, it is essential to sample the data. Testing small amounts of data helps ensure that it meets the requirements. It also limits the error rate and increases the accuracy and quality of the data. Testing involves a variety of methods and tools.
For excample use the worlds largest Data Validation Resource Center and pick the best use case.
Benefits of data validation
Data validation testing helps individuals ensure that the data gathered from different sources (whether it is structured or unstructured) meets the requirements. This benefit may seem obvious, but it enables several real-world applications that confer significant benefits.
Enhanced efficiency
Manual data processing (paper trails) slow down management operations and increase the likelihood of human error. For example, handling paper receipts and matching remittance details can delay accounting staff for hours. In this context, data validation testing an shorten the reconciliation process and limit the chances that fraud will occur. Since the processes are automated, there is also a limited risk that errors will occur.
With the improvements gained from data validation testing, financial departments can work faster with limited susceptibility to errors.
The continued fragmentation of the IT data industry resulting from the consumerization of computing resources, advanced performance analytics, and proliferation of third-party service offerings, has made it difficult for enterprises to comprehensively collect, integrate and process data so that it reveals meaningful, accurate analytics for decision making.
Unsurprisingly, as the complexity of IT systems continues to grow, so does the inaccuracy of enterprise data.
Studies suggest that over 90% of organizations suspect their prospects and customer data to be inaccurate, and research shows that nearly 50% of all business initiatives fail to attain their intended benefits.
It doesn't really matter how fast, how extensive, or how diverse the data types are. If the data collected is missing key features, is misaligned, or is unreliable, it will limit workflow effectiveness. It also cannot be used for decisionmaking models.
Better decision making
On many occasions, enterprises put their focus is diverted to data analysis without considering the complexity of managing the data collection process. However, leadership decisions are only as good as the data on which they are based, and the information itself is only as reliable as its level of accuracy.
Data validation provides the ability to mathematically evaluate the accuracy of the sources of data flows and apply scoring models during data consolidation, so the contents of the final dataset obtained are of the highest accuracy.
Using data validation testing, organizations can attain a comprehensive contextual history of its data across the environment, evaluate the time it will take to tackle and rectify current problems and predict future challenges.
In the case of customer databases, organizations often find themselves with datasets filled with outdated, incomplete, un-confirmed information data due to information being gathered from manual sources with inadequate electronic tools. With the right data validation tools, enterprises can attain real-time insight into their data, and use this to make accurate business decisions.
Whole-system Confidence
Developers are under a lot of pressure to constantly deliver results. Data validation testing can provide whole-system and component testing that help ensure the end result is as intended. As a matter of standard protocol, repeatable and robust testing is always advisable, and automated testing can accomplish this efficiently. Instead of testing individual sections and doing the occasional spot fix, you can do repeatable whole-system and component testing with every release to ensure that everything is working as it should.
It’s vital that you have confidence in the data you provide. Whether you're managing data testing with an in-house team (or choosing to outsource it), the quality of your data directly impacts the quality of your end user experience.
Popular data validation types
Sampling
Mappings (Business Rules Review)
Source-to-target mapping (business rules, mappings) is a set of data manipulation rules that control how the structure and contents of data in the source system are adapted to the requirements of the target system. Source to target mapping applications allow users to find columns or keys in the source system and point them to columns or keys in the target system. Furthermore, users can align data values in the source system to the range of importance in the target system.
A mapping document usually contains business rules.
The mapping document contains tables that provide conditions for extracting, transforming (where necessary), and loading data from the origin database and files into the target data warehouse. To be more specific:
- The mapping fields field names
- Table names, data types, and the length of both target and source fields.
- How source files/tables should be merged in the new target data set
- Any transformation logic required.
- Business rules to be applied.
Creating test cases
Each mapping usually has a unique test case. Test cases have two sets of SQL queries (or SQL for Hadoop). One query takes data from the sources (databases, flat files, web services, XML, and others), and the second query extracts data from the target (big data stores or data warehouses).
Executing Tests and Exporting Results
SQL editors (such as squirrel, toad, or others) are used to implement the tests, and the test results from the two queries are stored in two Excel spreadsheets.
Comparison
At this point, you'll have to physically compare all the result sets in the original spreadsheet with the target spreadsheet by eye. There will be lots of scrolling involved to compare tens or even hundreds of thousands of rows.
Disadvantages of Sampling
It is not possible to effectively compare millions of data sets. Consequently, less than 1% of the data is analyzed, making it highly likely that errors will occur.
Minus Queries
What Is a Minus Query?
A minus query uses the minus operator in SQL to find the difference between two datasets.
The SQL minus operator returns all rows with the first select statement that aren’t produced by the second select statement. Each select statement defines a dataset. The minus operator will take all records from the first data set and then remove from the results all records from the second dataset.
Note: The minus operator is unsupported in all SQL databases. It can be applied to databases such as oracle. However, for databases built-in PostgreSQL, SQL Server, and SQLite, you must use the except operator to perform a query.
Minus operator syntax
The syntax for a minus operator in SQL is as follows;
SELECT variable1, variable2, ...Variable_n from tables. [where conditions]
MINUS
SELECT variable1, variable2, ... Variable_n from tables [where conditions];
Arguments or parameters
Variable1, variable2, variable_n are the calculations or columns to be retrieved.
Tables are the tables from which records will be retrieved.
At the very least, there must be a table listed in the from clause.
Where conditions are not required. These are conditions that must be fulfilled before the records are selected.
Note: there must be an equal number of expressions in the first and second select statements.
The corresponding expressions must have a similar data type in the select statements. For instance, expression1 must have the same data type in both select statements.
How to test with minus queries
The way to carry out tests using minus queries is to perform target-minus-source and source-minus-target queries for all data, ensuring the data extraction process does not provide duplicates and that all irrelevant columns are removed before the data is loaded for validation.
Disadvantages of minus queries
Minus queries have several disadvantages. Because they do not return data whenever the comparison sets are the same, this may
- Hinder historical analysis or review of data from previous tests
- Hinder compliance requirements for organizations that must document analysis results for auditing
- Possible false-positive results.
Technical concerns
- Minus queries are performed either on the source or target database, and this may consume significant database resources (memory, CPU, and hard drive read/write processes).
- In the typical minus query implementation, minus queries must be run twice (target-to-source and source-to-target) this doubles resource usage and execution time.
- If directional minus queries are combined through a union (unions reduce the number of query executions by half) information on which side the extra rows were found may be lost.
- Result sets may not be accurate in the event that there are duplicate rows (the minus query may return just 1 row even when there are duplicates)
Production validation
Also referred to as production reconciliation or table balancing, this validation approach compares production system data with source data. The method prevents failed loads, flawed logic, and operational processes that aren’t loaded to the system.
Metadata testing
This test involves performing data type, length, constraint, and index checks of ETL application metadata, i.e. Reconciliation totals, load statistics, data quality metrics.
Performance testing
This helps ensure that data is loaded into the data warehouse within the expected period and that the test server response to several users and transactions is satisfactory for scalability and performance.
Data integration testing
This test ensures that all the data from all sources has loaded to the target data warehouse the right way. It also checks threshold values.
How to implement data validation
To adopt data validation for your projects, you'll need to implement three kinds of testing that should be applied as early in your architecture as possible. The staging area is the best place to start.
- Structural testing
- Functional testing
- Non-functional testing
You’ll also want to run any performance checks that you may need to, for example, to check whether the data integration load time meets your timeframe expectations or exceeds the time you’ve specified.
Structural database testing
Structural database testing involves validating all the contents of the data repository (these elements can’t be manipulated by end-users). Database server validation is also an essential part of structural database testing. Successful completion of this process requires knowledge of SQL queries.
Schema testing
Schema testing is a component of structural evaluation that corroborates whether the mapping formats of views/tables/columns conform to the mapping formats of the user interface. Its main purpose is to verify that the schema mapping between the front and back-end are the same.
Tools to use for database schema validation
Dbunit: it's integrated with ant and is ideal for mapping testing
SQL Server allows users to check and query database schema by writing simple queries.
Database table and column testing
You must check to ensure that
- The mapping of the database columns and fields is compatible with the mappings at the front end
- The length and naming conventions of the database fields and columns match the requirements.
- The database fields allow users to enter desired inputs as specified by the business requirement documents.
Keys and indexes testing
Important tests for indexes and keys:
- Check that the required foreign and primary key constraints have been created on the required tables
- Check that the foreign key references are valid
- Ensure that the data type of the primary key and corresponding foreign keys is similar in the two tables.
Stored procedures testing
You need to verify that development teams implemented the required
- exemption and error handling
- coding standard conventions
- database server validation
The database server configuration must also match the specified business requirements.
You should also:
- Check the authorization of the required users to ensure they perform only the required applications.
- Check that the database server is capable of handling the maximum acceptable number of user requests.
Functional database testing
Functional database testing is used to verify the functional capabilities of a database from the end-users perspective.
The main aim of functional database testing is to ensure the operations and transactions performed by the end-users work as expected.
Checking data consistency and integrity
The following checks are vital
- Is the data organized logically?
- Is the data accurate (as per the business requirements?)
- Are there unnecessary data in the application being tested?
- Has the data been stored as per the requirements?
- Are transactions rolled back successfully whenever transactions are not successfully executed by the end-users
- Do the transactions that occur follow the required design procedures as specified by system business requirement?
Non-functional testing
Non-functional testing (as it relates to database validation testing) is grouped into several categories. These may include stress testing, load testing, usability testing, and security testing. Although most of these tests do not directly mitigate the risk of data errors, they reveal opportunities for correction and remediation that will reduce the chances of errors.
Avoiding Common Mistakes
Not communicating changes is one of the most common mistakes in data validation. These resulting source schema issues like file name changes to files or tables, removal of data fields, or changes in data types happen for a number of reasons, but any of them can cause errors. Monitoring the schema source data for changes can help detect such issues.
Not maintaining the source system data is another issue: data cleansing and cleaning are important. It is advised not to use a cleaning mechanism as part of a data integration process, but to correct it in the source data first. Automatable test cases for all cleansing fixes help in the long term, since manual fixes can add human errors.Summary
Data validation is a crucial part of any data handling task. If your data isn't accurate from the beginning, your results won't be either. Whether you are gathering information in the field, analyzing data, or preparing a stakeholder presentation, this is one of the fundamental reasons you must validate data before using it.
Though often sidelined, data validation is an essential part of any data workflow. The reason for this is that data validation is perceived as an unrewarding yet challenging task. However, this perception is untrue because it is the only way to guarantee successful results from any project that requires data processing. While you can create a data validation process from scratch, these days, data quality automation platforms that include and automate data validation protocols can be used to efficiently implement data validation and deliver results quickly.
With these tools, you can smoothly integrate verification into your workflow, avoiding the laborious tasks it often entails.
Take a look in the worlds largest Data Validation Resource Center
Do the first step! Get in touch with BiG EVAL...
